""" db_softswitch realizes the interface of interaction with the 'SOFTSWITCH' database 
"""

import logging
from dbi import dbi


class db_softswitch(dbi):
    """ """
    def __init__(self, **kwargs):
        """ Constructor -- create the db connection. """
        dbi.__init__(self, **kwargs)
        self.log = logging.getLogger('db_softswitch')

    def select_caller_info(self, **kwargs):
        """ select_caller_info """
        print 'select_caller_info(%s)' % (kwargs)
        rec = {}
        rec['name'] = kwargs.get('name', None)
        rec['callerid'] = kwargs.get('callerid', None)
        if rec['name']:
            query = """SELECT u.accountcode, p.callerid, t.name, p.tester, u.surename, b.amount
                       FROM phones p, users u, balance b, tariffs t
                       WHERE p.name = %(name)s AND
                             u.id = p.uid AND
                             b.uid = p.uid AND
                             p.tariffid = t.id;"""
        elif rec['callerid']:
            query = """SELECT u.accountcode, p.callerid, t.name, p.tester, u.surename, b.amount
                       FROM phones p, users u, balance b, tariffs t
                       WHERE p.callerid = %(callerid)s AND
                             u.id = p.uid AND
                             b.uid = p.uid AND
                             p.tariffid = t.id;"""
        else:
            return None
        #print 'query: %s' % (query % rec)
        res = self.select(query, rec)
        #print 'res: %s' % res
        if not res:
            return None
        caller = {}
        caller['accountcode'] = res[0][0]
        caller['callerid'] = res[0][1]
        caller['tariff'] = res[0][2]
        caller['tester'] = res[0][3]
        caller['name'] = res[0][4]
        caller['balance'] = res[0][5]
        return caller

    def update_user_balance(self, **kwargs):
        """ update_user_balance """
        print 'update_user_balance(%s)' % (kwargs)
        rec = {}
        rec['accountcode'] = kwargs.get('accountcode', None)
        rec['callerid'] = kwargs.get('callerid', None)
        if rec['accountcode']:
            query = """SELECT uid
                       FROM users
                       WHERE accountcode = %(accountcode)s;"""
        elif rec['callerid']:
            query = """SELECT uid
                       FROM phones
                       WHERE callerid = %(callerid)s;"""
        else:
            return None
        #print 'query: %s' % (query % rec)
        res = self.select(query, rec)
        #print 'res: %s' % res
        if not res:
            return None
        balance = {}
        balance['uid'] = res[0][0]
        query = """SELECT id, amount
                   FROM balance
                   WHERE uid = %(uid)s;"""
        #print 'query: %s' % (query % balance)
        res = self.select(query, balance)
        #print 'res: %s' % res
        if not res:
            return None
        balance['id'] = res[0][0]
        balance['amount'] = res[0][1]
        balance['amount'] -= kwargs.get('cost', 0.0)
        query = """UPDATE balance
                   SET amount = %(amount)s
                   WHERE id = %(id)s;"""
        #print 'query: %s' % (query % balance)
        res = self.update(query, balance)
        print 'update user balance ok'
        return True

    def update_dialstatus(self, **kwargs):
        """ update_dialstatus """
        print 'update_dialstatus(%s)' % (kwargs)
        rec = {}
        rec['uniqueid'] = kwargs.get('uniqueid', None)
        rec['dst'] = kwargs.get('dest', None)
        rec['operator'] = kwargs.get('operator', None)
        rec['status'] = kwargs.get('status', None)
        rec['trunkname']= kwargs.get('trunkname', None)
        rec['billsec'] = kwargs.get('billsec', None)
        rec['duration'] = kwargs.get('duration', None)
        if rec['billsec']:
            query = """UPDATE dialstatus
                       SET billsec = %(billsec)s, duration = %(duration)s
                       WHERE uniqueid = %(uniqueid)s AND
                             operator = %(operator)s AND
                             trunkname = %(trunkname)s AND
                             dst = %(dst)s;"""
            res = self.update(query, rec)
        else:
            query = """INSERT INTO dialstatus (dst,operator,status,trunkname,uniqueid)
                       VALUES (%(dst)s,%(operator)s,%(status)s,%(trunkname)s,%(uniqueid)s);"""
            res = self.insert(query, rec)
        print 'upadte dialstatus table ok'
        return True
    
    def update_fullcdr(self, **kwargs):
        """ update_fullcdr """
        print 'update_fullcdr(%s)' % (kwargs)
        rec = {}
        #rec['calldate'] = kwargs.get('calldate', None)
        rec['calldate'] = kwargs.get('starttime', None)
        #rec['clid'] = kwargs.get('clid', None)
        rec['clid'] = kwargs.get('callerid', None)
        #rec['src'] = kwargs.get('src', None)
        rec['src'] = kwargs.get('source', None)
        #rec['dst'] = kwargs.get('dst', None)
        rec['dst'] = kwargs.get('destination', None)
        #rec['dcontext'] = kwargs.get('dcontext', None)
        rec['dcontext'] = kwargs.get('destinationcontext', None)
        rec['channel'] = kwargs.get('channel', None)
        #rec['dstchannel'] = kwargs.get('dstchannel', None)
        rec['dstchannel'] = kwargs.get('destinationchannel', None)
        #rec['lastapp'] = kwargs.get('lastapp', None)
        rec['lastapp'] = kwargs.get('lastapplication', None)
        rec['lastdata'] = kwargs.get('lastdata', None)
        rec['duration'] = kwargs.get('duration', None)
        #rec['billsec'] = kwargs.get('billsec', None)
        rec['billsec'] = kwargs.get('billableseconds', None)
        rec['disposition'] = kwargs.get('disposition', None)
        rec['amaflags'] = 0#kwargs.get('amaflags', None)
        rec['accountcode'] = kwargs.get('accountcode', None)
        rec['uniqueid'] = kwargs.get('uniqueid', None)
        rec['userfield'] = kwargs.get('userfield', None)
        rec['operator'] = kwargs.get('operator', None)
        rec['pricename'] = kwargs.get('pricename', None)
        rec['ppm'] = kwargs.get('ppm', None)
        rec['dialstatus'] = kwargs.get('dialstatus', None)
        rec['trunkname'] = kwargs.get('trunkname', None)
        rec['mypricename'] = kwargs.get('mypricename', None)
        rec['myppm'] = kwargs.get('myppm', None)
        rec['mydest'] = kwargs.get('mydest', None)
        rec['combinedcodec'] = kwargs.get('combinedcodec', None)

        query = """INSERT INTO fullcdr
                   (    calldate,
                        clid,
                        src,
                        dst,
                        dcontext,
                        channel,
                        dstchannel,
                        lastapp,
                        lastdata,
                        duration,
                        billsec,
                        disposition,
                        amaflags,
                        accountcode,
                        uniqueid,
                        userfield,
                        operator,
                        pricename,
                        ppm,
                        trunkname,
                        mypricename,
                        myppm,
                        mydest,
                        combinedcodec
                    )
                   VALUES
                   (    %(calldate)s,
                        %(clid)s,
                        %(src)s,
                        %(dst)s,
                        %(dcontext)s,
                        %(channel)s,
                        %(dstchannel)s,
                        %(lastapp)s,
                        %(lastdata)s,
                        %(duration)s,
                        %(billsec)s,
                        %(disposition)s,
                        %(amaflags)s,
                        %(accountcode)s,
                        %(uniqueid)s,
                        %(userfield)s,
                        %(operator)s,
                        %(pricename)s,
                        %(ppm)s,
                        %(trunkname)s,
                        %(mypricename)s,
                        %(myppm)s,
                        %(mydest)s,
                        %(combinedcodec)s
                    );"""
        query = """INSERT INTO fullcdr
                   (    calldate,
                        src,
                        dst,
                        dcontext,
                        duration,
                        billsec,
                        disposition,
                        accountcode,
                        uniqueid,
                        operator,
                        trunkname,
                        pricename,
                        ppm,
                        mypricename,
                        myppm,
                        mydest,
                        combinedcodec
                    )
                   VALUES
                   (    %(calldate)s,
                        %(src)s,
                        %(dst)s,
                        %(dcontext)s,
                        %(duration)s,
                        %(billsec)s,
                        %(disposition)s,
                        %(accountcode)s,
                        %(uniqueid)s,
                        %(operator)s,
                        %(trunkname)s,
                        %(pricename)s,
                        %(ppm)s,
                        %(mypricename)s,
                        %(myppm)s,
                        %(mydest)s,
                        %(combinedcodec)s
                    );"""
        res = self.insert(query, rec)
        print 'update fullcdr ok'
        return True

    def select_mycurprice(self, number):
        """ select_mycurprice """
        print 'select_mycurprice(%s)' % (number)
        price = {}
        number = str(number)
        rec = {}
        rec['name'] = 'mycurprice'
        query = """SELECT value
                   FROM selector
                   WHERE name = %(name)s;"""
        res = self.select(query, rec)
        if not res:
            return None
        price['pricename'] = res[0][0]
        checknum = len(number)
        rec['tablename'] = price['pricename']
        while checknum > 0:
            rec['checknum'] = number[:checknum]
            query = """SELECT ppm, dst
                       FROM %(tablename)s
                       WHERE dst LIKE %(checknum)s
                       ORDER BY length(dst) DESC;"""
            res = self.select(query % rec)
            if res:
                price['ppm'] = res[0][0]
                price['dest'] = res[0][1]
                break;
            checknum -= 1
        if not price.get('ppm', None):
            return None
        return price

    def select_callroutes(self, tariff, tester, number):
        """ select_callroutes """
        print 'select_callroutes (%s, %s, %s)' % (tariff, tester, number)
        trunks = []
        number = str(number)
        rec = {}
        rec['number'] = int(number)
        rec['tariff'] = tariff
        if tester:
            query = """SELECT value
                       FROM selector
                       WHERE name = 'testroute';"""
        else:
            query = """SELECT value
                       FROM selector
                       WHERE name = 'activeroute';"""
        res = self.select(query, rec)
        if not res:
            return None
        rec['tablename'] = res[0][0]
        #maxlendest = int(res[0][0])
        #checknum = min(len(number), maxlendest)
        checknum = len(number)
        trunknames = []
        strtrunknames = ''
        while checknum > 0:
            rec['checknum'] = number[:checknum]
            rec['trunknames'] = strtrunknames
            if strtrunknames:
                query = """SELECT dst,pricename,trunktype,trunkname,ppm,operator,techprefix
                           FROM """ + rec['tablename'] + """
                           WHERE dst LIKE %(checknum)s AND
                                 trunkname NOT IN (%(trunknames)s)
                           ORDER BY """ + rec['tariff'] + """ DESC;"""
            else:
                query = """SELECT dst,pricename,trunktype,trunkname,ppm,operator,techprefix
                           FROM """ + rec['tablename'] + """
                           WHERE dst LIKE %(checknum)s
                           ORDER BY """ + rec['tariff'] + """ DESC;"""
                           #ORDER BY %(tariff)s DESC;"""
            res = self.select(query, rec)
            if not res:
                checknum -= 1
                continue
            for bufftuple in res:
                trunk = {}
                trunk['dest'] = bufftuple[0]
                trunk['pricename'] = bufftuple[1]
                trunk['trunktype'] = bufftuple[2]
                trunk['trunkname'] = bufftuple[3]
                trunk['ppm'] = bufftuple[4]
                trunk['operator'] = bufftuple[5]
                trunk['techprefix'] = bufftuple[6]
                if trunk['trunkname'] not in trunknames:
                    trunknames.append(trunk['trunkname'])
                    if strtrunknames:
                        strtrunknames += "'," + trunk['trunkname'] + "'"
                    else:
                        strtrunknames += "'" + trunk['trunkname'] + "'"
                    trunks.append(trunk)
            checknum -= 1
        return trunks

    def select_testphones(self, **kwargs):
        """ select_testphones """
        print 'select_testphones(%s)' % (kwargs)
        testphones = []
        rec = {}
        query = """SELECT callerid, sip, iax
                   FROM phones
                   WHERE tester;"""
        res = self.select(query, rec)
        if not res:
            return []
        for row in res:
            phone = {}
            if row[1]:
                phone['type'] = 'SIP'
            else:
                phone['type'] = 'IAX'
            phone['callerid'] = row[0]
            testphones.append(phone)
        return testphones

    def select_testextens(self, **kwargs):
        """ select_testextens """
        print 'select_testextens(%s)' % (kwargs)
        testextens = []
        query = """SELECT exten
                   FROM testexten
                   WHERE enable;"""
        res = self.select(query)
        if not res:
            return []
        for row in res:
            testextens.append(row[0])
        return testextens

    def update_testdials(self, **kwargs):
        """ update_testdials """
        print 'update_testdials(%s)' % (kwargs)
        rec = {}
        rec['status'] = kwargs.get('status', None)
        query = """INSERT INTO testdials (status)
                   VALUES (%(status)s);"""
        res = self.insert(query, rec)
        print 'update_testdials ok'

    def update_testresults(self, **kwargs):
        """ update_testresults """
        print 'update_testresults(%s)' % (kwargs)
        rec = {}
        rec['start'] = kwargs.get('start', None)
        rec['finish'] = kwargs.get('finish', None)
        rec['call_amount'] = kwargs.get('call_amount', None)
        rec['answer_amount'] = kwargs.get('answer_amount', None)
        rec['success_amount'] = kwargs.get('success_amount', None)
        query = """INSERT INTO testresults (start,finish,call_amount,answer_amount,success_amount)
                   VALUES (%(start)s,%(finish)s,%(call_amount)s,%(answer_amount)s,%(success_amount)s);"""
        res = self.insert(query, rec)
        print 'update_testresults ok'
